package com.bxd.baodan.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

import com.j256.ormlite.android.apptools.OrmLiteSqliteOpenHelper;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.stmt.DeleteBuilder;
import com.j256.ormlite.stmt.UpdateBuilder;
import com.j256.ormlite.support.ConnectionSource;
import com.j256.ormlite.table.TableUtils;
import com.bxd.baodan.App;
import com.bxd.baodan.R;
import com.bxd.baodan.app.Constant;
import com.bxd.baodan.app.LoginInfo;
import com.bxd.baodan.item.CityItem;
import com.bxd.baodan.item.MessageItem;
import com.bxd.baodan.item.ProvinceItem;
import com.bxd.baodan.item.ServiceItem;

import org.json.JSONException;
import org.json.JSONObject;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

/**
 * 数据库帮助类
 * 
 * @author oldfeel
 * Å
 *         Create on: 2014年6月26日
 */
public class DBHelper extends OrmLiteSqliteOpenHelper {
	private static DBHelper dbHelper;
	private Context context;

	public static DBHelper getInstance(Context context) {
		if (dbHelper == null) {
			dbHelper = new DBHelper();
		}
		return dbHelper;
	}

	// 根据城市获取省份code
	public int getProvinceCode(String lastCarCity) {
		Dao<CityItem, ?> dao;
		try {
			dao = getDao(CityItem.class);
			CityItem cityItem = dao.queryBuilder().where()
					.eq("name", lastCarCity).queryForFirst();
			return cityItem.getProvinceCode();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	public DBHelper() {
		super(App.getContext(), Constant.DB_NAME, null,
				Constant.DB_VERSION);
		this.context = App.getContext();
	}

	@Override
	public void onCreate(SQLiteDatabase sqliteDatabase,
			ConnectionSource connectionSource) {
		try {
			TableUtils.createTable(connectionSource, MessageItem.class);
			TableUtils.createTable(connectionSource, CityItem.class);
			TableUtils.createTable(connectionSource, ProvinceItem.class);
			// 创建消息活动表
			// 创建问问主界面问题列表数据库表
			TableUtils.createTable(connectionSource, ServiceItem.class);
			initProvince();
			initCity();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase sqliteDatabase,
						  ConnectionSource connectionSource, int oldVer, int newVer) {
		try {
			TableUtils.dropTable(connectionSource, MessageItem.class, true);
			onCreate(sqliteDatabase, connectionSource);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 初始化市
	 */
	private void initCity() {
		try {
			InputStream inputStream = context.getResources().openRawResource(
					R.raw.city);
			BufferedReader reader = new BufferedReader(new InputStreamReader(
					inputStream));
			StringBuilder sb = new StringBuilder();
			String line;
			while ((line = reader.readLine()) != null) {
				sb.append(line.trim());
			}
			JSONObject json = new JSONObject(sb.toString());
			Iterator<?> iterator = json.keys();
			Dao<CityItem, ?> dao = getDao(CityItem.class);
			while (iterator.hasNext()) {
				String provinceCode = iterator.next().toString();
				JSONObject cityJson = json.getJSONObject(provinceCode);
				Iterator<?> cityIterator = cityJson.keys();
				while (cityIterator.hasNext()) {
					String cityCode = cityIterator.next().toString();
					String name = cityJson.getString(cityCode);
					CityItem item = new CityItem(Integer.valueOf(provinceCode),
							Integer.valueOf(cityCode), name);
					dao.create(item);
				}
			}
			inputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JSONException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 初始化省
	 */
	private void initProvince() {
		try {
			InputStream inputStream = context.getResources().openRawResource(
					R.raw.province);
			BufferedReader reader = new BufferedReader(new InputStreamReader(
					inputStream));
			StringBuilder sb = new StringBuilder();
			String line;
			while ((line = reader.readLine()) != null) {
				sb.append(line.trim());
			}
			JSONObject json = new JSONObject(sb.toString());
			Iterator<?> iterator = json.keys();
			Dao<ProvinceItem, ?> dao = getDao(ProvinceItem.class);
			while (iterator.hasNext()) {
				String provinceCode = iterator.next().toString();
				String name = json.getString(provinceCode);
				ProvinceItem item = new ProvinceItem(
						Integer.valueOf(provinceCode), name);
				dao.create(item);
			}
			inputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (JSONException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}



	/**
	 * 根据省码获取省
	 * 
	 * @param provinceCode
	 * @return
	 */
	public ProvinceItem getProvince(int provinceCode) {
		try {
			Dao<ProvinceItem, ?> dao = getDao(ProvinceItem.class);
			ProvinceItem item = dao.queryBuilder().where()
					.eq("provinceCode", provinceCode).queryForFirst();
			return item;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 根据城市码获取城市
	 * 
	 * @param cityCode
	 * @return
	 */
	public CityItem getCity(int cityCode) {
		try {
			Dao<CityItem, ?> dao = getDao(CityItem.class);
			CityItem item = dao.queryBuilder().where().eq("cityCode", cityCode)
					.queryForFirst();
			return item;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}




	/**
	 * 根据城市获取城市码
	 * 
	 * @param
	 * @return
	 */
	public CityItem getCityCode(String city) {
		try {
			Dao<CityItem, ?> dao = getDao(CityItem.class);
			List<CityItem> query = dao.queryBuilder().where().eq("name", city).query();
			CityItem item = dao.queryBuilder().where().eq("name", city)
					.queryForFirst();
			return item;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 清空消息表
	 */
	public void clearMessage() {
		try {
			Dao<MessageItem, ?> dao = getDao(MessageItem.class);
			DeleteBuilder<MessageItem, ?> builder = dao.deleteBuilder();
			builder.where().eq("uid",
					LoginInfo.getInstance(context).getUser().getU_id());
			builder.delete();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public long getNoReadCount(MessageItem messageItem) {
		try {
			Dao<MessageItem, ?> dao = getDao(MessageItem.class);
			List<MessageItem> list = dao.queryBuilder().where().eq("isread", false)
					.query();
			if (list != null) {
				return list.size();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public void getMessageListByPage(int page) {
		Dao<MessageItem, ?> dao;
		try {
			dao = getDao(MessageItem.class);
			List<MessageItem> messageItems = dao
					.queryBuilder()
					.limit(Constant.PAGE_SIZE)
					.offset(Constant.PAGE_SIZE * page)
					.orderBy("time", false)
					.groupBy("qid")
					.where()
					.eq("uid",
							LoginInfo
									.getInstance(App.getContext())
									.getUser().getU_id()).query();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 获取该问题下的未读消息数量
	 * 
	 * @param qid
	 * @return
	 */
	public long getNoReadCount(int qid) {
		try {
			return getDao(MessageItem.class).queryBuilder().where()
					.eq("qid", qid).and().eq("isread", false).countOf();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	/**
	 * 获取未读消息总条数
	 */
	public long getNotReadTotalCount() {
		try {
			Dao<MessageItem, ?> dao = getDao(MessageItem.class);
			List<MessageItem> list = dao.queryBuilder().where()
					.eq("isread", false).query();
			if (list != null) {
				return list.size();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	/**
	 * 删除消息
	 * 
	 * @param item
	 */
	public void delete(MessageItem item) {
		try {
			Dao<MessageItem, ?> dao = getDao(MessageItem.class);
			dao.delete(item);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 设置消息为已读
	 */
	public void setIsReaded(int qid) {
		try {
			Dao<MessageItem, ?> dao = getDao(MessageItem.class);
			UpdateBuilder<MessageItem, ?> builder = dao.updateBuilder();
			builder.updateColumnValue("isread", true);
			builder.where().eq("qid", qid);
			builder.update();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取存储的导航页信息
	 */
	public List<ServiceItem> getServiceItem() {
		try {
			Dao<ServiceItem, ?> dao = getDao(ServiceItem.class);
			List<ServiceItem> temp = dao.queryBuilder().query();
			return temp;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 存储返回的导航页信息
	 */
	public void initServiceItem(List<ServiceItem> list) {
		deleteServiceItem();
		try {
			for (ServiceItem item : list) {
				Dao<ServiceItem, ?> dao = getDao(ServiceItem.class);
				dao.create(item);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 删除导航页信息
	 */
	public void deleteServiceItem() {
		try {
			Dao<ServiceItem, ?> dao;
			dao = getDao(ServiceItem.class);
			DeleteBuilder<ServiceItem, ?> builder = dao.deleteBuilder();
			builder.delete();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
